﻿/* 
 * 
 * Copyright 2013 Bendsoft. All rights reserved.
 * http://www.bendsoft.com
 * 
 * This code is distributed under the New BSD License (BSD).
 * 
 * @package     Camelot SharePoint Integration Library
 * @codeplex    http://camelottoolkit.codeplex.com
 * @license     New BSD License (BSD)
 * 
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Camelot.SharePointConnector.Data;
using Camelot.SharePointIntegration.IntegrationService.Entities;
using Camelot.SharePointIntegration.Client;

namespace Camelot.SharePointIntegration
{
    public sealed class ListDataUtility : ListData
    {
        private ListDataUtility() { }

        public static ListData CreateListData(string listName, string viewName, string[] columns, bool includeAttachments, SharePointConnection connection)
        {
            var listId = Guid.Empty;
            if (System.Text.RegularExpressions.Regex.IsMatch(listName, @"^(([0-9a-f]{8})\-([0-9a-f]{4})\-([0-9a-f]{4})\-([0-9a-f]{4})\-([0-9a-f]{12})|\{([0-9a-f]{8})\-([0-9a-f]{4})\-([0-9a-f]{4})\-([0-9a-f]{4})\-([0-9a-f]{12})\})$", System.Text.RegularExpressions.RegexOptions.IgnoreCase))
                listId = new Guid(listName);

            using (var siteLists = new DataTable())
            {
                Utilities.ExecuteDataTable("show tables", connection, siteLists);
                foreach (DataRow row in siteLists.Rows)
                {
                    if (((Guid)row["ID"]).CompareTo(listId) == 0 || ((string)row["Title"]).ToLower() == listName.ToLower())
                    {
                        var data = new ListDataUtility()
                        {
                            Description = new ListDescription()
                            {
                                ID = (Guid)(row["ID"]),
                                Title = (string)(row["Title"]),
                                Description = (string)(row["Description"]),
                                Created = (DateTime)(row["Created"]),
                                Modified = (DateTime)(row["Modified"]),
                                LastDeleted = (DateTime)(row["LastDeleted"]),
                                ItemCount = (Int32)(row["ItemCount"]),
                                BaseType = (ListType)(row["BaseType"])
                            }
                        };

                        var dsContent = new DataSet("Content");

                        if (columns == null)
                        {
                            var dtSchema = new DataTable("Schema");
                            if (!String.IsNullOrEmpty(viewName))
                            {
                                Utilities.ExecuteDataTable(String.Format("show fields from `{0}`.`{1}`", data.Description.ID.ToString(), viewName), connection, dtSchema);
                            }
                            else
                            {
                                Utilities.ExecuteDataTable(String.Format("show fields from `{0}`", data.Description.ID.ToString()), connection, dtSchema);
                            }
                            dsContent.Tables.Add(dtSchema);
                        }

                        var dtRows = new DataTable("Rows");
                        if (!String.IsNullOrEmpty(viewName))
                        {
                            Utilities.ExecuteDataTable(String.Format("select {0} from `{1}`.`{2}`", columns != null ? columns.Select(c => "`" + c + "`").Aggregate((c1, c2) => c1 + ", " + c2) : "*", data.Description.ID.ToString(), viewName), connection, dtRows);
                        }
                        else
                        {
                            Utilities.ExecuteDataTable(String.Format("select {0} from `{1}`", columns != null ? columns.Select(c => "`" + c + "`").Aggregate((c1, c2) => c1 + ", " + c2) : "*", data.Description.ID.ToString()), connection, dtRows);
                        }
                        dsContent.Tables.Add(dtRows);

                        if (includeAttachments && dtRows.Columns.Contains("ID"))
                        {
                            var dtAttachments = new DataTable("Attachments");

                            foreach (DataRow itemRow in dtRows.Rows)
                            {
                                if (!dtRows.Columns.Contains("Attachments") || (bool)(itemRow["Attachments"]) == true)
                                    Utilities.ExecuteDataTable(String.Format("select * from `{0}`.attachments where itemid = {1}", data.Description.ID.ToString(), itemRow["ID"].ToString()), connection, dtAttachments);
                            }

                            if (dtAttachments.Rows.Count > 0)
                            {
                                dsContent.Tables.Add(dtAttachments);
                                dsContent.Relations.Add("Attachments", dtRows.Columns["ID"], dtAttachments.Columns["ItemID"]);
                            }
                            else
                            {
                                dtAttachments.Dispose();
                                dtAttachments = null;
                            }
                        }

                        data.Content = dsContent;
                        return data;
                    }
                }
            }
            return null;
        }

        public static ListData CreateListData(string listName, string viewName, bool includeAttachments, SharePointConnection connection)
        {
            return CreateListData(listName, viewName, null, includeAttachments, connection);
        }

        public static ListData CreateListData(string sqlCommand, SharePointConnection connection)
        {
            var data = new ListDataUtility();

            var dtRows = new DataTable("Rows");
            if (!String.IsNullOrEmpty(sqlCommand))
                Utilities.ExecuteDataTable(sqlCommand, connection, dtRows);

            data.Content = new DataSet("Content");
            data.Content.Tables.Add(dtRows);

            return data;
        }

        public static ListData CreateListData(string sqlCommand, SharePointConnection connection, int skip, int take)
        {
            var data = new ListDataUtility();

            var dtRows = new DataTable("Rows");
            if (!String.IsNullOrEmpty(sqlCommand))
                Utilities.ExecuteDataTable(sqlCommand, connection, dtRows);

            EnumerableRowCollection<DataRow> dtRowsEnumerable = dtRows.AsEnumerable();
            var count = dtRowsEnumerable.Count();

            if (skip < count)
            {
                if (skip + take > count)
                    take = count - skip;

                dtRows = dtRowsEnumerable.Skip(skip).Take(take).CopyToDataTable<DataRow>();
                dtRows.TableName = "Rows";

                data.Content = new DataSet("Content");
                data.Content.Tables.Add(dtRows);

                return data;
            }

            return data;
        }

        public static ListData CreateListData(Task task)
        {
            using (var connection = new SharePointConnection(task.Command.ConnectionString))
            {
                connection.Open();

                ListData listData;
                if (String.IsNullOrEmpty(task.Command.SqlCommand))
                    listData = CreateListData(task.Command.ListName, task.Command.ViewName, task.Command.IncludeAttachments, connection);
                else
                    listData = CreateListData(task.Command.SqlCommand, connection);

                return listData;
            }
        }
    }
}